package club.pchome.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExcelUtil2 {
	private static Logger log = LoggerFactory.getLogger(ExcelUtil2.class);
	private static SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	/**
	 * 读取excel文档的第一个表
	 * 
	 * @param is
	 * @return
	 */
	public static List<Map<String, Object>> readExcelFirstSheet(InputStream is) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			Workbook workbook = WorkbookFactory.create(is);// 这种方式Excel,2003,2007,2010,都是可以处理的

			Sheet sheet = workbook.getSheetAt(0);// 读取第一个表
			int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数
			// 遍历每一行
			for (int r = 1; r < rowCount; r++) {
				Row row = sheet.getRow(r);
//				int cellCount = row.getPhysicalNumberOfCells(); // 获取总列数
				
				int cellCount = 2; // 获取总列数

				Map<String, Object> map = new HashMap<String, Object>();

				// 遍历每一列
				for (int c = 0; c < cellCount; c++) {
					String cellValue = ExcelUtil2.formatCellValue(row.getCell(c));
					
					map.put("cell_" + c, cellValue);
				}
				list.add(map);
				System.out.println(map.get("cell_0"));
			}
		} catch (Exception e) {
			log.error("读取excel文档的第一个表异常:", e);
		}
		return list;
	}

	/**
	 * 格式化单元个数据
	 * 
	 * @param cell
	 * @return
	 */
	private static String formatCellValue(Cell cell) {
		int cellType = cell.getCellType();
		String cellValue = null;
		switch (cellType) {
		case Cell.CELL_TYPE_STRING: // 文本
			cellValue = cell.getStringCellValue();
			break;
		case Cell.CELL_TYPE_NUMERIC: // 数字、日期
			if (DateUtil.isCellDateFormatted(cell)) {
				cellValue = fmt.format(cell.getDateCellValue()); // 日期型
			} else {
				cellValue = String.valueOf(new BigDecimal(cell.getNumericCellValue()));// 数字
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN: // 布尔型
			cellValue = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_BLANK: // 空白
			cellValue = cell.getStringCellValue();
			break;
		case Cell.CELL_TYPE_ERROR: // 错误
			cellValue = "错误";
			break;
		case Cell.CELL_TYPE_FORMULA: // 公式
			cellValue = "错误";
			break;
		default:
			cellValue = "错误";
		}
		return cellValue;
	}

	/**
	 * 读取excel文档的第一个表
	 * 
	 * @param path
	 * @return
	 */
	public static List<Map<String, Object>> readExcelFirstSheet(String path) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			File file = new File(path);
			InputStream is = new FileInputStream(file);
			list = ExcelUtil2.readExcelFirstSheet(is);
		} catch (Exception e) {
			log.error("读取excel文档的第一个表异常:", e);
		}
		return list;
	}
	
	public static void main(String[] args) {
		readExcelFirstSheet("e:/ADM数据表.xlsx");
		
	}

	/**
	 * 设置导出头部信息(xls格式)
	 * 
	 * @param response
	 * @param fileName
	 */
	public static void setResponseForXls(HttpServletResponse response, String fileName) {
		response.setCharacterEncoding("utf-8");
		response.setContentType("multipart/form-data");
		response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
	}
}